const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./student-course.db');

db.serialize(() => {
  // 创建用户表
  db.run(`CREATE TABLE IF NOT EXISTS Users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    role TEXT CHECK(role IN ('student', 'teacher', 'admin')) NOT NULL,
    name TEXT NOT NULL
  )`);

  // 创建课程表
  db.run(`CREATE TABLE IF NOT EXISTS Courses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    teacher_id INTEGER NOT NULL,
    credit INTEGER NOT NULL,
    time TEXT NOT NULL,
    location TEXT NOT NULL,
    FOREIGN KEY(teacher_id) REFERENCES Users(id)
  )`);

  // 创建选课表
  db.run(`CREATE TABLE IF NOT EXISTS Enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    grade REAL,
    PRIMARY KEY(student_id, course_id),
    FOREIGN KEY(student_id) REFERENCES Users(id),
    FOREIGN KEY(course_id) REFERENCES Courses(id)
  )`);

  console.log('数据库表创建完成');
});

db.close();
const path = require('path');

// 定义数据库文件路径
const dbPath = path.join(__dirname, '..', 'database', 'student_course.db');

// 打开数据库连接
const db = new sqlite3.Database(dbPath, (err) => {
    if (err) {
        console.error('数据库连接失败:', err.message);
    } else {
        console.log('成功连接到 SQLite 数据库');
        // 执行表创建和数据初始化
        createTables();
    }
});

// 创建表的函数
function createTables() {
    // 用户表
    const createUserTable = `
        CREATE TABLE IF NOT EXISTS Users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username VARCHAR(50) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            name VARCHAR(100) NOT NULL,
            role ENUM('admin', 'teacher', 'student') NOT NULL,
            email VARCHAR(100) UNIQUE,
            phone VARCHAR(20),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        );
    `;

    // 课程表
    const createCourseTable = `
        CREATE TABLE IF NOT EXISTS Courses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            code VARCHAR(20) UNIQUE NOT NULL,
            name VARCHAR(100) NOT NULL,
            description TEXT,
            credit DECIMAL(3,1) NOT NULL,
            capacity INTEGER NOT NULL,
            current_enrollment INTEGER DEFAULT 0,
            teacher_id INTEGER NOT NULL,
            location VARCHAR(100),
            schedule VARCHAR(100),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (teacher_id) REFERENCES Users(id)
        );
    `;

    // 选课记录表
    const createEnrollmentTable = `
        CREATE TABLE IF NOT EXISTS Enrollments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id INTEGER NOT NULL,
            course_id INTEGER NOT NULL,
            status ENUM('selected', 'dropped') DEFAULT 'selected',
            score DECIMAL(4,1),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE(student_id, course_id),
            FOREIGN KEY (student_id) REFERENCES Users(id),
            FOREIGN KEY (course_id) REFERENCES Courses(id)
        );
    `;

    // 执行建表语句
    db.serialize(() => {
        db.run(createUserTable, (err) => {
            if (err) {
                console.error('创建用户表失败:', err.message);
            } else {
                console.log('用户表创建成功');
            }
        });

        db.run(createCourseTable, (err) => {
            if (err) {
                console.error('创建课程表失败:', err.message);
            } else {
                console.log('课程表创建成功');
            }
        });

        db.run(createEnrollmentTable, (err) => {
            if (err) {
                console.error('创建选课记录表失败:', err.message);
            } else {
                console.log('选课记录表创建成功');
            }
        });
    });

    // 关闭数据库连接
    db.close((err) => {
        if (err) {
            console.error('关闭数据库失败:', err.message);
        } else {
            console.log('数据库已关闭');
        }
    });
}